set feedback off
set define off

prompt !!!!!!!!!!!!!!
prompt The user have to have the create table privilege via: GRANT CREATE TABLE TO username;
prompt !!!!!!!!!!!!!!

prompt Creating DQ_META_COLUMN...
create table DQ_META_COLUMN
(
  ID          NUMBER,
  TABLE_ID    NUMBER,
  NAME        VARCHAR2(200),
  "DATATYPE"    VARCHAR2(200),
  VARIABLE_YN VARCHAR2(1)
)
;


prompt Creating DQ_META_DEPENDENCY...
create table DQ_META_DEPENDENCY
(
  ID           NUMBER,
  NAME         VARCHAR2(255),
  AUTHOR       VARCHAR2(255),
  CREATIONDATE DATE,
  TYPE_ID      NUMBER,
  ORDER_ID     NUMBER
)
;

prompt Creating DQ_META_DEPENDENCY_TYPE...
create table DQ_META_DEPENDENCY_TYPE
(
  ID   NUMBER,
  NAME VARCHAR2(255)
)
;

prompt Creating DQ_META_DEP_COL_ASSIGN...
create table DQ_META_DEP_COL_ASSIGN
(
  ID            NUMBER,
  COLUMN_ID     NUMBER,
  DEPENDENCY_ID NUMBER,
  SITE_ID       NUMBER,
  DEP_TABLE_ID  NUMBER
)
;

prompt Creating DQ_META_DEP_COL_JOIN_ASSIGN...
create table DQ_META_DEP_COL_JOIN_ASSIGN
(
  ID               NUMBER,
  SOURCE_COLUMN_ID NUMBER,
  TARGET_COLUMN_ID NUMBER
)
;

prompt Creating DQ_META_DEP_ITER_ASSIGN...
create table DQ_META_DEP_ITER_ASSIGN
(
  ID            NUMBER not null,
  DEPENDENCY_ID NUMBER not null,
  ITERATION_ID  NUMBER not null
)
;

prompt Creating DQ_META_DEP_TAB_ASSIGN...
create table DQ_META_DEP_TAB_ASSIGN
(
  ID            NUMBER,
  TABLE_ID      NUMBER,
  DEPENDENCY_ID NUMBER,
  SITE_ID       NUMBER
)
;

prompt Creating DQ_META_DEP_TAB_JOIN_ASSIGN...
create table DQ_META_DEP_TAB_JOIN_ASSIGN
(
  ID                        NUMBER,
  PRIMARY_TABLE_ID          NUMBER,
  SECONDARY_TABLE_ID        NUMBER,
  PRIMARY_TABLE_COLUMN_ID   NUMBER,
  SECONDARY_TABLE_COLUMN_ID NUMBER
)
;

prompt Creating DQ_META_RUN...
create table DQ_META_RUN
(
  ID         NUMBER not null,
  START_DATE DATE not null
)
;

prompt Creating DQ_META_RUN_ITERATION...
create table DQ_META_RUN_ITERATION
(
  ID               NUMBER,
  RUN_ID           NUMBER,
  ITERATION_NUMBER NUMBER
)
;

prompt Creating DQ_META_SCHEMA...
create table DQ_META_SCHEMA
(
  ID       NUMBER not null,
  NAME     VARCHAR2(255) not null,
  DATATYPE VARCHAR2(255)
)
;
alter table DQ_META_SCHEMA
  add constraint PK_SCHEMA_ID primary key (ID);

prompt Creating DQ_META_SITE...
create table DQ_META_SITE
(
  ID   NUMBER,
  NAME VARCHAR2(255)
)
;

prompt Creating DQ_META_TABLE...
create table DQ_META_TABLE
(
  SCHEMA_ID NUMBER,
  NAME      VARCHAR2(200),
  ID        NUMBER,
  PRIMARY   VARCHAR2(200)
)
;

prompt Creating DQ_META_TARGET_STAR_JOIN...
create table DQ_META_TARGET_STAR_JOIN
(
  ID                        NUMBER,
  PRIMARY_TABLE_ID          NUMBER,
  SECONDARY_TABLE_ID        NUMBER,
  PRIMARY_TABLE_COLUMN_ID   NUMBER,
  SECONDARY_TABLE_COLUMN_ID NUMBER,
  ORDER_ID                  NUMBER
)
;

prompt Creating DQ_META_VARIABLE...
create table DQ_META_VARIABLE
(
  ID       NUMBER,
  VALUE    VARCHAR2(200),
  DATATYPE VARCHAR2(200)
)
;



CREATE SEQUENCE  "DQ_META_RUN_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;
CREATE SEQUENCE  "DQ_META_RUN_ITERATION_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;
CREATE SEQUENCE  "DQ_META_DEP_ITER_ASSIGN_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;
CREATE OR REPLACE TRIGGER dq_meta_dep_iter_assign_trg
  BEFORE INSERT
  ON dq_meta_dep_iter_assign
  FOR EACH ROW
  -- Optionally restrict this trigger to fire only when really needed
  
when (new.id is null)
DECLARE
  v_id dq_meta_dep_iter_assign.id%TYPE;
BEGIN
  -- Select a new value from the sequence into a local variable. As David
  -- commented, this step is optional. You can directly select into :new.qname_id
  SELECT dq_meta_dep_iter_assign_seq.nextval INTO v_id FROM DUAL;

  -- :new references the record that you are about to insert into qname. Hence,
  -- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
  -- obtained from your sequence, before inserting
  :new.id := v_id;
END my_trigger;
/

CREATE OR REPLACE TRIGGER dq_meta_run_iteration_trg
  BEFORE INSERT
  ON dq_meta_run_iteration
  FOR EACH ROW
  -- Optionally restrict this trigger to fire only when really needed
  
when (new.id is null)
DECLARE
  v_id dq_meta_run_iteration.id%TYPE;
BEGIN
  -- Select a new value from the sequence into a local variable. As David
  -- commented, this step is optional. You can directly select into :new.qname_id
  SELECT dq_meta_run_iteration_seq.nextval INTO v_id FROM DUAL;

  -- :new references the record that you are about to insert into qname. Hence,
  -- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
  -- obtained from your sequence, before inserting
  :new.id := v_id;
END my_trigger;
/
CREATE OR REPLACE TRIGGER dq_meta_run_trg
  BEFORE INSERT
  ON dq_meta_run
  FOR EACH ROW
  -- Optionally restrict this trigger to fire only when really needed
  
when (new.id is null)
DECLARE
  v_id dq_meta_run.id%TYPE;
BEGIN
  -- Select a new value from the sequence into a local variable. As David
  -- commented, this step is optional. You can directly select into :new.qname_id
  SELECT dq_meta_run_seq.nextval INTO v_id FROM DUAL;

  -- :new references the record that you are about to insert into qname. Hence,
  -- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
  -- obtained from your sequence, before inserting
  :new.id := v_id;
END my_trigger;
/

prompt Loading DQ_META_COLUMN...
prompt Table is empty
prompt Loading DQ_META_DENORM_TARGET...
prompt Table is empty
prompt Loading DQ_META_DEPENDENCY...
prompt Table is empty
prompt Loading DQ_META_DEPENDENCY_TYPE...
prompt Table is empty
prompt Loading DQ_META_DEP_COL_ASSIGN...
prompt Table is empty
prompt Loading DQ_META_DEP_COL_JOIN_ASSIGN...
prompt Table is empty
prompt Loading DQ_META_DEP_ITER_ASSIGN...
prompt Table is empty
prompt Loading DQ_META_DEP_TAB_ASSIGN...
prompt Table is empty
prompt Loading DQ_META_DEP_TAB_JOIN_ASSIGN...
prompt Table is empty
prompt Loading DQ_META_RUN...
prompt Table is empty
prompt Loading DQ_META_RUN_ITERATION...
prompt Table is empty
prompt Loading DQ_META_SCHEMA...
prompt Table is empty
prompt Loading DQ_META_SITE...
prompt Table is empty
prompt Loading DQ_META_TABLE...
prompt Table is empty
prompt Loading DQ_META_TARGET_STAR_JOIN...
prompt Table is empty
prompt Loading DQ_META_VARIABLE...
prompt Table is empty
set feedback on
set define on
prompt Done.
